package com.studentachievement.utils;

import java.sql.*;

public class DatabaseUtil {
    final static private String url = "jdbc:mysql://localhost:3306/student_achievement?useSSL=false&serverTimezone=UTC";
    final static private String username = "root";
    final static private String password = "root";
    static {//保证在web中也能正确加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("Failed to load MySQL driver.");
        }
    }

    public static Connection getConnection() throws SQLException {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException("连接数据库失败", e);
        }
    }
//    public static Connection getConnection_() {
//        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            //Class.forName("com.mysql.jdbc.Driver");
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        }
//        String url="jdbc:mysql://localhost:3306/student_achievement";
//        Connection conn=null;
//        try {
//            conn = DriverManager.getConnection(url,username ,password);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return conn;
//    }

    public static Connection getConnection_() {
        Connection conn = null;
        try {
            // 明确加载 MySQL 驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 获取连接
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            System.err.println("Driver class not found: " + e.getMessage());
        } catch (SQLException e) {
            System.err.println("SQL exception: " + e.getMessage());
        }
        return conn;
    }
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void close_(Connection conn, Statement stmt, ResultSet rs){
        close(conn, stmt, rs);
    }
    public static void MakeStudentprimaryKeyContiguous(){
        Connection conn =  null;
        Statement stmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            stmt = conn.createStatement();
            // 初始化变量并重新编号
            stmt.execute("SET @i = 0");
            stmt.execute("UPDATE students SET student_id = (@i := @i + 1) ORDER BY student_id");
            // 重置自增主键
            stmt.execute("ALTER TABLE students AUTO_INCREMENT = 1");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    public static void MakeCourseprimaryKeyContiguous(){
        Connection conn =  null;
        Statement stmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            stmt = conn.createStatement();
            // 初始化变量并重新编号
            stmt.execute("SET @i = 0");
            stmt.execute("UPDATE courses SET course_id = (@i := @i + 1) ORDER BY course_id");
            // 重置自增主键
            stmt.execute("ALTER TABLE courses AUTO_INCREMENT = 1");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
    public static void MakeScoreprimaryKeyContiguous(){
        Connection conn =  null;
        Statement stmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            stmt = conn.createStatement();
            // 初始化变量并重新编号
            stmt.execute("SET @i = 0");
            stmt.execute("UPDATE scores SET score_id = (@i := @i + 1) ORDER BY score_id");
            // 重置自增主键
            stmt.execute("ALTER TABLE scores AUTO_INCREMENT = 1");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
}
